{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1db4dbc9",
   "metadata": {
    "id": "task-02-python自动化之excel"
   },
   "source": [
    "#  Task 02 Python Excel 自动化之 OpenPyXL"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eb7d323e",
   "metadata": {
    "id": "2-0-包的安装"
   },
   "source": [
    "## 2.0 包的安装\n",
    "\n",
    "操作难度：⭐\n",
    "\n",
    "打开 CMD/Terminal 进入到自己环境后，执行下面语句安装`openpyxl`模块。\n",
    "```bash\n",
    "pip3 install openpyxl\n",
    "```\n",
    "\n",
    "注：openpyxl可以读/写 .xlsx /.xlsm /.xltx /.xltm 的格式文件，但是不支持去读 /.xls 格式；读取 xls 格式，可以安装 **xlrd** 模块，`pip3 install xlrd`，本章节以 /.xlsx 格式为主。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7eec9b3",
   "metadata": {
    "id": "2-1-excel读取"
   },
   "source": [
    "## 2.1 Excel读取\n",
    "\n",
    " 项目难度：⭐\n",
    "\n",
    " - Excel 全称为 Microsoft Office Excel，2003年版本的是 xls 格式，2007和2007年之后的版本是 xlsx 格式。\n",
    " - xlsx 格式通过 `openpyxl` 模块打开； xls 格式通过 `xlwt` 模块写，`xlrd` 模块读取。\n",
    " - 本文以 xlsx 模式为例"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c17ab602",
   "metadata": {
    "id": "2-1-1-读取对应表格"
   },
   "source": [
    "### 2.1.1 读取Excel中的工作表\n",
    "\n",
    "**关于路径：**\n",
    "\n",
    "文件应在当前工作目录才可直接用相对路径引用，可导入`os`，使用函数`os.getcwd()`弄清楚当前工作目录是什么，可使用`os.chdir()`改变当前工作目录，具体可参考第一章节。（此处显现为相对路径）"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "92c48912",
   "metadata": {
    "id": "2-1-1-读取对应表格-code"
   },
   "outputs": [],
   "source": [
    "# 获取当前工作目录\n",
    "import os\n",
    "os.getcwd()\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "root_path = './OpenPyXL_test/'"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fef81965",
   "metadata": {
    "id": "2-1-1-读取对应表格-2"
   },
   "source": [
    "#### 1. 读取Excel文件 `用户行为偏好.xlsx ` ，查看返回值属性"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "f5abca0f",
   "metadata": {
    "id": "2-1-1-读取对应表格-code-2"
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "openpyxl.workbook.workbook.Workbook"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 导入模块，查看属性\n",
    "import openpyxl\n",
    "\n",
    "wb = openpyxl.load_workbook(root_path+'用户行为偏好.xlsx')\n",
    "type(wb)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f7f17ead",
   "metadata": {
    "id": "2-1-1-读取对应表格-3"
   },
   "source": [
    "【代码解释】\n",
    "\n",
    "这里我们使用 openpyxl 中的 load_workbook 函数来加载指定的 xlsx 文件，。\n",
    "- openpyxl.load_workbook(\n",
    "    filename,\n",
    "    read_only=False,\n",
    "    keep_vba=False,\n",
    "    data_only=False,\n",
    "    keep_links=True,\n",
    ")\n",
    "\n",
    "load_workbook 函数有五个参数，除 filename 外，其他参数都有默认值，各参数含义如下：\n",
    "\n",
    "- `filename`: str 类型，表示要打开的文件的相对/绝对路径；\n",
    "- `read_only`: bool 类型，是否以只读模式打开文件，默认值为 False，可读写；\n",
    "- `keep_vba`: bool 类型，是否保留文件中的 vba 内容（即使保留了也不一定在代码中能使用），默认值为 False，不保留；\n",
    "- `data_only`: bool 类型，如果单元格中是 excel 公式，是以公式计算后的值的形式显示还是以公式内容形式显示，默认值为 False，以公式内容形式展示；\n",
    "- `keep_links`: bool 类型，是否保留单元格中的外链，默认值为 True，保留外链；\n",
    "\n",
    "- 返回值类型: `openpyxl.workbook.Workbook`\n",
    "\n",
    "如无特殊要求，我们只需要指定`filename`参数即可。\n",
    "\n",
    "\n",
    "【小知识】\n",
    "\n",
    "**import * 和from...import...**\n",
    "\n",
    "`import *`和`from...import...`的区别\n",
    "\n",
    " - `import`导入一个模块，相当于导入的是一个文件夹，相对路径。\n",
    " - `from...import...`导入了一个模块中的一个函数，相当于文件夹中的文件，绝对路径。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe7dc5c3",
   "metadata": {},
   "source": [
    "#### 2. 查看对应工作簿包含的 sheet(工作表) 的名称，读取活动表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "ed04f863",
   "metadata": {
    "id": "2-1-1-读取对应表格-code-3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['订单时长分布', 'Sheet3']\n"
     ]
    }
   ],
   "source": [
    "# 导入模块中的函数，查询对应表的名称\n",
    "print(wb.sheetnames)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14df09b7",
   "metadata": {},
   "source": [
    "【代码解释】\n",
    "\n",
    "这里我们使用 `openpyxl.workbook.Workbook` 类对象的 `sheetnames` 属性来获取读取的工作簿中包含的 sheet(工作表) 的名称。\n",
    "\n",
    "通过上述代码输出内容，我们可以知道 `用户行为偏好.xlsx` 中包含两个 sheet(工作表)，分别是：订单时长分布、 Sheet3。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "2e299f4d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "active_sheet对象: <Worksheet \"订单时长分布\">\n",
      "active_sheet 名称: 订单时长分布\n"
     ]
    }
   ],
   "source": [
    "# 读取工作簿的活动表\n",
    "# 活动表是工作簿在 Excel 中打开时出现的工作表，在取得 Worksheet 对象后，可通过 title 属性取得它的名称。\n",
    "active_sheet = wb.active\n",
    "print(f'active_sheet对象: {active_sheet}')\n",
    "print(f'active_sheet 名称: {active_sheet.title}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "68d564d6",
   "metadata": {},
   "source": [
    "【小知识】\n",
    "\n",
    "活动表是可以修改的，在我们正常打开excel，完成修改后，保存excel，在关闭 excel 前显示的 sheet 就是活动表。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4e56b9d0",
   "metadata": {},
   "source": [
    "#### 3. 查看指定sheet信息"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "36d6b9da",
   "metadata": {
    "id": "2-1-1-读取对应表格-code-4"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheet: <Worksheet \"Sheet3\">\n",
      "type(sheet): <class 'openpyxl.worksheet.worksheet.Worksheet'>\n",
      "sheet.title: Sheet3\n",
      "sheet.dimensions: A1:I17\n"
     ]
    }
   ],
   "source": [
    "# 通过传递表名字符串读取表、类型和名称、内容占据的大小\n",
    "sheet = wb.get_sheet_by_name('Sheet3')\n",
    "print(f'sheet: {sheet}')\n",
    "print(f'type(sheet): {type(sheet)}')\n",
    "print(f'sheet.title: {sheet.title}')\n",
    "print(f'sheet.dimensions: {sheet.dimensions}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "87960ed8",
   "metadata": {},
   "source": [
    "【代码解释】\n",
    "\n",
    "这里我们使用 `openpyxl.workbook.Workbook` 类对象的 `get_sheet_by_name` 方法，通过指定 sheetname 的方式来获取读取的工作簿中指定的 sheet(工作表) 对象。\n",
    "\n",
    "并使用 `openpyxl.worksheet.worksheet.Worksheet` 类对象的一些属性来获取 sheet 的基本信息，比如 `Worksheet.title`获取 sheet 名称，`Worksheet.dimensions` 获取 sheet 中值的范围。\n",
    "\n",
    "\n",
    "Workbook.get_sheet_by_name(name) 函数只有一个参数，就是：sheetname(工作表名称)，功能是：通过 sheetname 获取到 Worksheet 对象，除了通过函数的方式获取到 Worksheet 对象，你还可以提过索引的方式，如：\n",
    "```python\n",
    "wb['Sheet3']\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e09235e5",
   "metadata": {
    "id": "2-1-2-读取单元格"
   },
   "source": [
    "### 2.1.2 读取工作表中的单元格\n",
    "\n",
    "![image-20211110131533928](./imgs/2.3.png)\n",
    "\n",
    "**Cell(Excel单元格)**\n",
    "\n",
    " - Cell 对象有一个 value 属性，包含这个单元格中保存的值。\n",
    " - Cell 对象也有 row 、column 和 coordinate 属性，提供该单元格的位置信息。\n",
    " - Excel 用字母指定列，在Z列之后，列开始使用两个字母：AA、AB等，所以在调用的 cell() 方法时，可传入整数作为 row 和 column 关键字参数，也可以得到一个单元格。\n",
    " - 注：第一行或第一列的整数取1，而不是0."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "6c4ee45a",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheetnames: ['订单时长分布', 'Sheet3']\n"
     ]
    }
   ],
   "source": [
    "# 从表中取得单元格 在 2.1.1 中我们已经读取过工作簿了 返回结果存储变量为 wb\n",
    "## 获取表格名称\n",
    "print(f'sheetnames: {wb.sheetnames}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "2210c227",
   "metadata": {
    "id": "2-1-2-读取单元格-code"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheet[B1']: <Cell '订单时长分布'.B1>\n",
      "sheet[B1'].value: 日期\n",
      "Row: 1, Column: 2\n",
      "Cell B1 is 日期\n"
     ]
    }
   ],
   "source": [
    "# 获取指定sheet\n",
    "sheet = wb.get_sheet_by_name('订单时长分布')\n",
    "\n",
    "# 通过单元格位置获取单元格对象，如：B1\n",
    "a = sheet['B1']\n",
    "print(f\"sheet[B1']: {a}\")\n",
    "\n",
    "# 获取并打印 B1 单元格的文本内容\n",
    "print(f\"sheet[B1'].value: {a.value}\")\n",
    "\n",
    "# 获取并打印 B1 单元格所在行、列和数值\n",
    "print(f'Row: {a.row}, Column: {a.column}')\n",
    "\n",
    "# 获取并打印 B1 单元格坐标 和 值\n",
    "print(f'Cell {a.coordinate} is {a.value}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "6b44286c",
   "metadata": {
    "id": "2-1-2-读取单元格-code-2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1 日期\n",
      "3 2020-07-24 00:00:00\n",
      "5 2020-07-24 00:00:00\n",
      "7 2020-07-24 00:00:00\n"
     ]
    }
   ],
   "source": [
    "# 获取并打印出 B列 前8行的奇数行单元格的值\n",
    "for i in range(1,8,2):\n",
    "    print(i, sheet.cell(row=i,column=2).value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "274e6d75",
   "metadata": {
    "id": "2-1-2-读取单元格-code-3"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheet.max_row: 14\n",
      "sheet.max_column: 4\n"
     ]
    }
   ],
   "source": [
    "# 确定表格的最大行数和最大列数，即表的大小\n",
    "print(f'sheet.max_row: {sheet.max_row}')\n",
    "print(f'sheet.max_column: {sheet.max_column}')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fd4220cc",
   "metadata": {
    "id": "2-1-3-读取多个格子的值"
   },
   "source": [
    "### 2.1.3 读取多个单元格的值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "38abc41e",
   "metadata": {
    "id": "2-1-3-读取多个格子的值-code"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "type(cells): <class 'tuple'> \n",
      "\n",
      "编号 |日期 |行为时长 |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |a |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |b |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |c |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |d |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |e |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |f |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |g |\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 方法一：直接通过sheet索引，A1到C8区域的值\n",
    "cells = sheet['A1:C8']\n",
    "print(f'type(cells): {type(cells)} \\n')\n",
    "\n",
    "# 遍历元组 print每一个cell值\n",
    "for rows in cells:\n",
    "    for cell in rows:\n",
    "        print(cell.value, end=\" |\")\n",
    "    print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "e7eb5f27",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "编号 |日期 |行为时长 |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |a |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |b |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |c |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |d |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |e |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |f |\n",
      "\n",
      "71401.30952380953 |2020-07-24 00:00:00 |g |\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 方法二：sheet.iter_rows函数 按行获取数据\n",
    "rows = sheet.iter_rows(min_row=1, max_row=8, min_col=1, max_col=3)\n",
    "# 遍历元组 print每一个cell值\n",
    "for row in rows:\n",
    "    for cell in row:\n",
    "        print(cell.value, end=\" |\")\n",
    "    print(\"\\n\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "37528729",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "编号 |71401.30952380953 |71401.30952380953 |71401.30952380953 |\n",
      "\n",
      "日期 |2020-07-24 00:00:00 |2020-07-24 00:00:00 |2020-07-24 00:00:00 |\n",
      "\n",
      "行为时长 |a |b |c |\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 方法三：sheet.iter_cols函数 按列获取数据\n",
    "cols = sheet.iter_cols(min_row=1, max_row=4, min_col=1, max_col=3)\n",
    "# 遍历元组 print每一个cell值\n",
    "for col in cols:\n",
    "    for cell in col:\n",
    "        print(cell.value, end=\" |\")\n",
    "    print(\"\\n\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2d846963",
   "metadata": {
    "id": "2-1-4-练习题"
   },
   "source": [
    "### 2.1.4 练习题\n",
    "\n",
    "找出`用户行为偏好.xlsx`中 Sheet3 表中空着的格子，并输出这些格子的坐标"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "57198086",
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import load_workbook\n",
    "\n",
    "exl = load_workbook(root_path+'用户行为偏好.xlsx')\n",
    "sheet3 = exl.get_sheet_by_name('Sheet3')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "bc044cb4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'A1:I17'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet3.dimensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4cf6917c",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "D3 is None \n",
      "\n",
      "D8 is None \n",
      "\n",
      "G10 is None \n",
      "\n"
     ]
    }
   ],
   "source": [
    "# 直接通过sheet索引，sheet3.dimensions获取sheet数据区域\n",
    "cells = sheet3[sheet3.dimensions]\n",
    "\n",
    "# 遍历元组 判断每一个cell值是否为空\n",
    "for rows in cells:\n",
    "    for cell in rows:\n",
    "        if not cell.value:\n",
    "            print(f'{cell.coordinate} is None \\n')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a7116ab",
   "metadata": {
    "id": "2-2-excel写入"
   },
   "source": [
    "## 2.2 Excel写入\n",
    "\n",
    " 项目难度：⭐"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5856b38d",
   "metadata": {
    "id": "2-2-1-写入数据并保存"
   },
   "source": [
    "### 2.2.1 写入数据并保存\n",
    "\n",
    "#### 1. 原有工作簿中修改数据并保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "c4edd41b",
   "metadata": {
    "id": "2-2-1-写入数据并保存-code"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "修改前 sheet['A1']: 1\n",
      "修改后 sheet['A1']: hello world\n"
     ]
    }
   ],
   "source": [
    "# 1) 导入 openpyxl 中的 load_workbook 函数\n",
    "from openpyxl import load_workbook\n",
    "\n",
    "# 2) 获取指定 excel文件对象 Workbook\n",
    "exl = load_workbook(filename=root_path+'用户行为偏好.xlsx')\n",
    "# 3) 通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet\n",
    "sheet = exl.get_sheet_by_name('Sheet3')\n",
    "# 4) 通过索引方式获取指定 cell 值，并重新赋值\n",
    "print(f\"修改前 sheet['A1']: {sheet['A1'].value}\")\n",
    "sheet['A1'].value = 'hello world'\n",
    "print(f\"修改后 sheet['A1']: {sheet['A1'].value}\")\n",
    "# 5) 保存修改后的内容\n",
    "# 如果 filename 和原文件同名，则是直接在原文件中修改；\n",
    "# 否则会新建一个 excel 文件，并保存内容\n",
    "exl.save(filename=root_path+'用户行为偏好_1.xlsx')  # 保存到一个新文件中 新文件名称为：用户行为偏好_1.xlsx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "041e4c4b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "修改保存成功啦～，exl_1['Sheet3']['A1'].value = hello world\n"
     ]
    }
   ],
   "source": [
    "# 验证保存修改内容是否成功\n",
    "exl_1 = load_workbook(filename=root_path+'用户行为偏好_1.xlsx')\n",
    "# 我们将原表中 Sheet3 中的 A1 值改为了 'hello world'\n",
    "# 所以读取保存文件，查看对应值是否为 'hello world' 即可\n",
    "a1 = exl_1['Sheet3']['A1'].value\n",
    "if a1 == 'hello world':\n",
    "    print(f\"修改保存成功啦～，exl_1['Sheet3']['A1'].value = {a1}\")\n",
    "else:\n",
    "    print(f\"修改保存有问题，现在exl_1['Sheet3']['A1'].value = {a1}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "54202196",
   "metadata": {},
   "source": [
    "【代码解释】\n",
    "\n",
    "从这里我们可以看到，我们只需要获取到 sheet 中的 cell 对象后，就可以通过改变 cell.value 的值来改变 对应单元格中的值，然后使用 Workbook 对象的 save 函数可以将修改后的工作簿内容保存起来。"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75e8000b",
   "metadata": {
    "id": "2-2-1-写入数据并保存-2"
   },
   "source": [
    "#### 2. 创建新的表格写入数据并保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "cd076831",
   "metadata": {
    "id": "2-2-1-写入数据并保存-code-2"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "默认sheet：['Sheet']\n",
      "添加后sheet：['mysheet', 'Sheet']\n",
      "sheet['A1'].value = this is test\n"
     ]
    }
   ],
   "source": [
    "# 1) 导入 openpyxl 中的 Workbook 类\n",
    "from openpyxl import Workbook\n",
    "\n",
    "# 2) 初始化一个 Workbook 对象\n",
    "wb = Workbook()\n",
    "print(f'默认sheet：{wb.sheetnames}')\n",
    "\n",
    "# 3) 通过 Workbook 对象的 create_sheet 函数创建一个 sheet\n",
    "# title sheet 名称\n",
    "# index sheet 位置，默认从0开始\n",
    "sheet = wb.create_sheet(title='mysheet', index=0)\n",
    "print(f'添加后sheet：{wb.sheetnames}')\n",
    "\n",
    "# 4) 在新建的 sheet 中写入数据\n",
    "# 比如 在 A1 单元格中写入 'this is test'\n",
    "sheet['A1'].value = 'this is test'\n",
    "\n",
    "print(f\"sheet['A1'].value = {sheet['A1'].value}\")\n",
    "\n",
    "# 保存\n",
    "wb.save(root_path+'creat_sheet_test.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "190280f2",
   "metadata": {
    "id": "2-2-2-将公式写入单元格保存"
   },
   "source": [
    "### 2.2.2 将公式写入单元格保存"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "4555eed5",
   "metadata": {
    "id": "2-2-2-将公式写入单元格保存-code"
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "订单时长分布 值范围: A1:D14\n"
     ]
    }
   ],
   "source": [
    "# 1) 导入 openpyxl 中的 load_workbook 函数\n",
    "from openpyxl import load_workbook\n",
    "\n",
    "# 2) 获取指定 excel文件对象 Workbook\n",
    "exl_1 = load_workbook(filename=root_path+'用户行为偏好_1.xlsx')\n",
    "# 3) 通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet\n",
    "sheet = exl_1['订单时长分布']\n",
    "\n",
    "print(f'订单时长分布 值范围: {sheet.dimensions}')      #先查看原有表格的单元格范围，防止替代原有数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "e8f9b494",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单元格 A15 中写入 合计\n",
    "sheet['A15'].value = '合计'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "a0155422",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 单元格 D15 中写入求和公式：SUM(D2:D14)\n",
    "sheet['D15'] = '=SUM(D2:D14)'\n",
    "exl_1.save(filename='用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "d79db2ac",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 使用 xlwings 打开 excel 文件然后保存 使写入的 公式生效\n",
    "import xlwings as xw \n",
    "# 打开工作簿\n",
    "app = xw.App(visible=False, add_book=False)\n",
    "wb = app.books.open('用户行为偏好_1.xlsx')  \n",
    "wb.save()\n",
    "# 关闭工作簿\n",
    "wb.close()\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "676189a5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "sheet['A15']=合计，sheet['D15']=4004.7261561561563\n",
      "次数 求和值为SUM(D2:D14)=4004.7261561561563\n"
     ]
    }
   ],
   "source": [
    "# 验证写入是否成功\n",
    "# 1) 获取指定 excel文件对象 Workbook，\n",
    "#    并设置 data_only=True，表示读取的时候如果单元格内是公式的话，以公式计算后的值的形式显示\n",
    "exl_2 = load_workbook(filename = '用户行为偏好_1.xlsx', data_only=True)\n",
    "# 2) 打印相关信息\n",
    "sheet = exl_2['订单时长分布']\n",
    "print(f\"sheet['A15']={sheet['A15'].value}，sheet['D15']={sheet['D15'].value}\")\n",
    "print(f\"{sheet['D1'].value} 求和值为SUM(D2:D14)={sheet['D15'].value}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5574dad4",
   "metadata": {},
   "source": [
    "【注意】\n",
    "\n",
    "即使设置了 data_only=True，也不能立即获取到刚刚添加的公式计算后的结果，需要自己 手动/添加代码 打开下 对应excel表格，然后 ctrl s保存下，再运行上面代码才能获取到对应公式计算后的值。\n",
    "\n",
    "你可以使用下面代码自动打开指定 excel 文件然后保存使写入的公式生效，使用前你需要安装 xlwings，输入`pip3 install xlwings`即可，再后面我们也会学习这个模块。\n",
    "\n",
    "```python\n",
    "# 使用 xlwings 打开 excel 文件然后保存 使写入的 公式生效\n",
    "import xlwings as xw \n",
    "# 打开工作簿\n",
    "app = xw.App(visible=False, add_book=False)\n",
    "wb = app.books.open('用户行为偏好_1.xlsx')  \n",
    "wb.save()\n",
    "# 关闭工作簿\n",
    "wb.close()\n",
    "app.quit()\n",
    "```"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5d42c2f0",
   "metadata": {
    "id": "2-2-3-插入数据"
   },
   "source": [
    "### 2.2.3 插入空列/行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "dffbb9d2",
   "metadata": {
    "id": "2-2-3-插入数据-code"
   },
   "outputs": [],
   "source": [
    "# 获取指定 sheet\n",
    "sheet = exl_1['Sheet3']\n",
    "\n",
    "# 插入列数据 insert_cols(idx,amount=1)\n",
    "# idx是插入位置，amount是插入列数，默认是1\n",
    "# idx=2第2列，第2列前插入一列\n",
    "sheet.insert_cols(idx=2)\n",
    "# 第2列前插入5\n",
    "# sheet.insert_cols(idx=2, amount=5)\n",
    "\n",
    "# 插入行数据 insert_rows(idx,amount=1)\n",
    "# idx是插入位置，amount是插入行数，默认是1\n",
    "# 在第二行前插入一行\n",
    "sheet.insert_rows(idx=2)\n",
    "# 第2行前插入5行\n",
    "# sheet.insert_rows(idx=2, amount=5)\n",
    "\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9ab10867",
   "metadata": {
    "id": "2-2-4-删除"
   },
   "source": [
    "### 2.2.4 删除"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "78a0a7a5",
   "metadata": {
    "id": "2-2-4-删除-code"
   },
   "outputs": [],
   "source": [
    "# 删除多列\n",
    "sheet.delete_cols(idx=5, amount=2)\n",
    "# 删除多行\n",
    "sheet.delete_rows(idx=2, amount=5)\n",
    "\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8741cff9",
   "metadata": {
    "id": "2-2-5-移动"
   },
   "source": [
    "### 2.2.5 移动\n",
    "\n",
    "当数字为正即向下或向右，为负即为向上或向左"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "id": "2b8c10db",
   "metadata": {
    "id": "2-2-5-移动-code"
   },
   "outputs": [],
   "source": [
    "# 移动\n",
    "# 当数字为正即向下或向右，为负即为向上或向左\n",
    "sheet.move_range('B3:E16',rows=1,cols=-1)\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "79f08c22",
   "metadata": {
    "id": "2-3-excel-样式"
   },
   "source": [
    "## 2.3 Excel 样式\n",
    "\n",
    " 项目难度：⭐⭐"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c69bf198",
   "metadata": {
    "id": "2-3-1设置字体样式"
   },
   "source": [
    "### 2.3.1设置字体样式\n",
    "\n",
    "#### 1. 设置单个 cell(单元格) 字体样式\n",
    "\n",
    "   `Font(name字体名称,size大小,bold粗体,italic斜体,color颜色)`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "31c2973f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1) 导入 openpyxl 中的 load_workbook 函数\n",
    "#    导入 openpyxl 中的  styles 模块中的 Font 类\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl.styles import Font\n",
    "\n",
    "# 2) 获取指定 excel文件对象 Workbook\n",
    "exl_1 = load_workbook(filename=root_path+'用户行为偏好_1.xlsx')\n",
    "# 3) 通过指定 sheetname 从 Workbook 中获取 sheet 对象 Worksheet\n",
    "sheet = exl_1['订单时长分布']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "595c0586",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<openpyxl.styles.fonts.Font object>\n",
       "Parameters:\n",
       "name='宋体', charset=134, family=3.0, b=True, i=False, strike=None, outline=None, shadow=None, condense=None, color=<openpyxl.styles.colors.Color object>\n",
       "Parameters:\n",
       "rgb=None, indexed=None, auto=None, theme=1, tint=0.0, type='theme', extend=None, sz=11.0, u=None, vertAlign=None, scheme='minor'"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4) 获取到指定 cell 后，查看cell字体属性\n",
    "cell = sheet['A1']\n",
    "cell.font"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0ced67e5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5) 实例化一个 Font 对象，设置字体样式\n",
    "#    字体改为：黑体  大小改为：20  设置为：加粗 斜体 红色\n",
    "font = Font(name='黑体', size=20, bold=True, italic=True, color='FF0000')\n",
    "cell.font = font\n",
    "# 6) 保存修改 \n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4b156a36",
   "metadata": {},
   "source": [
    "#### 2. 设置多个 cell 的字体样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "50da63a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表\n",
    "# 我们处理了 单元格 A1 的字体样式，我们也可以通过遍历的形式，批量设置单元格字体样式\n",
    "\n",
    "# 1) 获取要处理的单元格 \n",
    "#    通过 sheet 索引获取第二行 cell\n",
    "#    获取列可以用 字母索引，如 sheet['A'] 获取第一列 cell\n",
    "cells = sheet[2]\n",
    "# 2) 实例化一个 Font 对象，设置字体样式\n",
    "#    字体改为：黑体  大小改为：10  设置为：加粗 斜体 红色\n",
    "font = Font(name='黑体', size=10, bold=True, italic=True, color='FF0000')\n",
    "# 3) 遍历给每一个 cell 都设置上对应字体样式\n",
    "for cell in cells:\n",
    "    cell.font = font\n",
    "# 4) 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f5a3697d",
   "metadata": {
    "id": "2-3-2-设置对齐样式"
   },
   "source": [
    "### 2.3.2 设置边框样式\n",
    "\n",
    "#### 1. 设置单元格边框样式\n",
    "\n",
    "`Side`：边线样式设置类，边线颜色等\n",
    "\n",
    "Side(style=None, color=None, border_style=None)\n",
    "\n",
    "- style：边线的样式，有以下值可选：double, mediumDashDotDot, slantDashDot, dashDotDot, dotted, hair, mediumDashed, dashed, dashDot, thin, mediumDashDot, medium, thick\n",
    "- color：边线颜色\n",
    "- border_style：style 的别名，必须设置，一般直接设置 border_style 就行，不用设置 style\n",
    "\n",
    "`Border`：边框定位类，左右上下边线\n",
    "\n",
    "Border常用参数解释：\n",
    "\n",
    "- top bottom left right diagonal：上下左右和对角线的边线样式，为 Side 对象\n",
    "- diagonalDown：对角线从左上角向右下角方向，默认为 False\n",
    "- diagonalUp：对角线从右上角向左下角方向，默认为 False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "57681141",
   "metadata": {
    "id": "2-3-2-设置对齐样式-code"
   },
   "outputs": [],
   "source": [
    "# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet\n",
    "# 1) 导入 openpyxl 中的  styles 模块中的 Side, Border 类\n",
    "from openpyxl.styles import Side, Border\n",
    "# 2) 首先初始化一个边线对象（也可以设置多个）\n",
    "side = Side(border_style='double', color='FF000000')\n",
    "# 3) 通过 Border 去设置 整个单元格边框样式\n",
    "border = Border(left=side, right=side, top=side, bottom=side, diagonal=side, diagonalDown=True, diagonalUp=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "4ccd40bb",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<openpyxl.styles.borders.Border object>\n",
       "Parameters:\n",
       "outline=True, diagonalUp=False, diagonalDown=False, start=None, end=None, left=<openpyxl.styles.borders.Side object>\n",
       "Parameters:\n",
       "style=None, color=None, right=<openpyxl.styles.borders.Side object>\n",
       "Parameters:\n",
       "style=None, color=None, top=<openpyxl.styles.borders.Side object>\n",
       "Parameters:\n",
       "style=None, color=None, bottom=<openpyxl.styles.borders.Side object>\n",
       "Parameters:\n",
       "style=None, color=None, diagonal=<openpyxl.styles.borders.Side object>\n",
       "Parameters:\n",
       "style=None, color=None, vertical=None, horizontal=None"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 4) 查看目前单元格边框样式\n",
    "# 获取第一行 cells\n",
    "cells = sheet[1]\n",
    "# 取出一个 cell 看边框样式\n",
    "cells[0].border"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "id": "7b117185",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5) 修改边框样式，并保存修改\n",
    "for cell in cells:\n",
    "    cell.border = border\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "635f69cb",
   "metadata": {},
   "source": [
    "### 2.3.3 设置单元格其他样式"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f43a758a",
   "metadata": {
    "id": "2-3-2-设置对齐样式-2"
   },
   "source": [
    "#### 1. 设置单元格背景色"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "22d6cb6c",
   "metadata": {
    "id": "2-3-2-设置对齐样式-code-2"
   },
   "outputs": [],
   "source": [
    "# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet\n",
    "# 1) 从 openpyxl.styles 中导入 背景颜色设置类 PatternFill, GradientFill\n",
    "from openpyxl.styles import PatternFill, GradientFill\n",
    "\n",
    "# 2) 实例化 PatternFill 对象，fill_type 参数必须指定\n",
    "pattern_fill = PatternFill(fill_type='solid',fgColor=\"DDDDDD\")\n",
    "# 3) 实例化 GradientFill 对象，填充类型 type 默认为 linear\n",
    "gradient_fill = GradientFill(stop=('FFFFFF', '99ccff','000000'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "id": "cd3e52c1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4) 获取指定 cells 遍历填充\n",
    "# 对第三行 PatternFill 模式设置背景色\n",
    "cells = sheet[3]\n",
    "for cell in cells:\n",
    "    cell.fill = pattern_fill\n",
    "\n",
    "# 对第四行 GradientFill 模式设置背景色\n",
    "cells = sheet[4]\n",
    "for cell in cells:\n",
    "    cell.fill = gradient_fill\n",
    "\n",
    "# 5) 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f998cfe",
   "metadata": {},
   "source": [
    "#### 2.设置水平居中\n",
    "\n",
    "openpyxl.styles 中的 Alignment 类常用参数介绍：\n",
    "\n",
    "- horizontal：水平对齐，常见值 `distributed, justify, center, left, fill, centerContinuous, right, general`\n",
    "- vertical：垂直对齐，常见值 `bottom, distributed, justify, center, top`\n",
    "- textRotation：文字旋转角度，数值：0-180\n",
    "- wrapText：是否自动换行，bool值，默认 False"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "7f282fc6",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 中的 订单时长分布 工作表 sheet\n",
    "# 1) 从 openpyxl.styles 中导入 对齐方式设置类 Alignment\n",
    "from openpyxl.styles import Alignment\n",
    "\n",
    "# 2) 实例化一个 Alignment 对象，设置水平、垂直居中\n",
    "alignment = Alignment(horizontal='center', vertical='center')\n",
    "\n",
    "# 3) 获取指定 cells 遍历填充\n",
    "# 对第五行数据设置上面的对齐方式\n",
    "cells = sheet[5]\n",
    "for cell in cells:\n",
    "    cell.alignment = alignment\n",
    "# 4) 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bd4b0870",
   "metadata": {
    "id": "2-3-3-设置行高与列宽"
   },
   "source": [
    "#### 3. 设置行高与列宽"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "8a5743cd",
   "metadata": {
    "id": "2-3-3-设置行高与列宽-code"
   },
   "outputs": [],
   "source": [
    "# 1) 设置行高，通过 row_dimensions 和 column_dimensions 来获取行和列对象\n",
    "# 2) 设置第1行行高为 30\n",
    "sheet.row_dimensions[1].height = 30\n",
    "# 3) 设置第3列列款为 24\n",
    "sheet.column_dimensions['C'].width = 24\n",
    "# 4) 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "142d0bc7",
   "metadata": {
    "id": "2-3-4-合并-取消合并单元格"
   },
   "source": [
    "### 2.3.3 合并、取消合并单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "id": "b909f52e",
   "metadata": {
    "id": "2-3-4-合并-取消合并单元格-code"
   },
   "outputs": [],
   "source": [
    "# 注意：合并后的单元格只会显示合并区域中最右上角的单元格的值，会导致其他单元格内容丢失\n",
    "# 上面我们已经获取到了 '用户行为偏好_1.xlsx' 对象 exl_1，我们可以通过 exl_1 来索引获取自己想要的 sheet\n",
    "# 1) 获取 Sheet3 这个工作表\n",
    "sheet = exl_1['Sheet3']\n",
    "\n",
    "# 合并指定区域单元格\n",
    "sheet.merge_cells('A1:B2')\n",
    "\n",
    "# sheet.merge_cells(start_row=1, start_column=3,\n",
    "#                  end_row=2, end_column=4)\n",
    "\n",
    "# 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "b90bd977",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 解除合并\n",
    "sheet.unmerge_cells('A1:B2')\n",
    "\n",
    "# sheet.unmerge_cells(start_row=1, start_column=3,\n",
    "#                     end_row=2, end_column=4)\n",
    "\n",
    "# 保存修改\n",
    "exl_1.save(filename=root_path+'用户行为偏好_1.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "03183f6f",
   "metadata": {
    "id": "2-3-5-练习题"
   },
   "source": [
    "### 2.3.5 练习题\n",
    "\n",
    "打开 test.xlsx 文件，找出文件中购买数量 `buy_mount` 超过5的单元格，并对其标红、加粗、加上红色边框。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "a43cfda9",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1) 导入 openpyxl 相关函数和类\n",
    "from openpyxl import load_workbook\n",
    "from openpyxl.styles import Font, Side, Border\n",
    "\n",
    "# 2) 读取 test.xlsx 文件，并筛选出 buy_mount 这一列\n",
    "workbook = load_workbook(root_path+'test.xlsx')\n",
    "sheet = workbook.active\n",
    "buy_mount = sheet['B']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "97e451d5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3) 设置边框 文字样式\n",
    "side = Side(style='thin', color='FF0000')\n",
    "border = Border(left=side, right=side, top=side, bottom=side)\n",
    "font = Font(bold=True, color='FF0000')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "fea82fac",
   "metadata": {
    "id": "2-3-5-练习题-code"
   },
   "outputs": [],
   "source": [
    "# 4) 遍历判断 cell 值是否满足筛选条件\n",
    "for cell in buy_mount:\n",
    "    if isinstance(cell.value, float) and cell.value > 5:\n",
    "        cell.font = font\n",
    "        cell.border = border\n",
    "# 5) 修改内容另存为 new_test.xlsx\n",
    "workbook.save(root_path+'new_test.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5a93e0db",
   "metadata": {},
   "source": [
    "## 2.4 综合练习\n",
    "\n",
    "### 2.4.1 将 业务联系表.xlsx 拆分成以下两个 excel：\n",
    "- 客户信息表：客户名称 客户地址 客户方负责人 性别 联系电话 对接业务经理编号\n",
    "- 业务经理信息表：业务经理编号 所在分区 所在区域 业务经理姓名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "d1b95ca3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 1) 导入 openpyxl 相关函数和类\n",
    "from openpyxl import load_workbook, Workbook\n",
    "\n",
    "# 2) 读取原表数据\n",
    "wb = load_workbook(root_path+'业务联系表.xlsx')\n",
    "# 3) 获取工作表\n",
    "sheet = wb.active"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "b28ffae0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "A2 业务经理编号\n",
      "B2 分区\n",
      "C2 区域\n",
      "D2 业务经理\n",
      "E2 客户名称\n",
      "F2 客户地址\n",
      "G2 客户方负责人\n",
      "H2 性别\n",
      "I2 联系电话\n",
      "J2 备注\n"
     ]
    }
   ],
   "source": [
    "# 草稿纸 \n",
    "# 我们知道我们表格的实际列名在第二行\n",
    "# 获取每列第二行的坐标和值\n",
    "for i in sheet[2]:\n",
    "    print(i.coordinate, i.value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "2afe5665",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(10, 57)"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sheet.max_column, sheet.max_row"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "75a0c43f",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4) 筛选出需要的列\n",
    "# 4.1) 客户信息表：客户名称 客户地址 客户方负责人 性别 联系电话 备注 对接业务经理编号\n",
    "cust_info = {'业务经理编号': 'A', '客户名称': 'B', '客户地址': 'C', '客户方负责人': 'D', '性别': 'E', '联系电话': 'F', '备注': 'G'}\n",
    "\n",
    "# 4.2) 新建一个工作簿，并将默认sheet名称改成 客户信息\n",
    "cust_info_excel = Workbook()\n",
    "cust_info_sh = cust_info_excel.active\n",
    "cust_info_sh.title = '客户信息'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "9e7993d3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 4.3) 遍历筛选，如果是需要的表头，就将该列的值复制到新的工作簿中的 客户信息 工作表中\n",
    "for i in sheet[2]:\n",
    "    if i.value in cust_info:\n",
    "        # 遍历将这一列中除了第一个cell外的所有cell值复制到新表\n",
    "        for cell in sheet[i.coordinate[0]]:\n",
    "            if cell.row == 1:\n",
    "                continue\n",
    "            cust_info_sh[f'{cust_info[i.value]}{cell.row-1}'].value = cell.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "a54cba1b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5) 筛选出需要的列\n",
    "# 5.1) 业务经理信息表：业务经理编号 所在分区 所在区域 业务经理姓名\n",
    "manager_info = {'业务经理编号': 'A', '分区': 'B', '区域': 'C', '业务经理': 'D'}\n",
    "\n",
    "# 5.2) 新建一个工作簿，并将默认sheet名称改成 客户信息\n",
    "manager_info_excel = Workbook()\n",
    "manager_info_sh = manager_info_excel.active\n",
    "manager_info_sh.title = '业务经理信息'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f8532945",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 5.3) 遍历筛选，如果是需要的表头，就将该列的值复制到新的工作簿中的 业务经理信息 工作表中\n",
    "for i in sheet[2]:\n",
    "    if i.value in manager_info:\n",
    "        # 遍历将这一列中除了第一个cell外的所有cell值复制到新表\n",
    "        for cell in sheet[i.coordinate[0]]:\n",
    "            if cell.row == 1:\n",
    "                continue\n",
    "            manager_info_sh[f'{manager_info[i.value]}{cell.row-1}'].value = cell.value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "90756f96",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 6.1 ) 保存 客户信息表 工作簿内容\n",
    "cust_info_excel.save(root_path+'客户信息表_xl.xlsx')\n",
    "# 6.2) 保存 业务经理信息表 工作簿内容\n",
    "manager_info_excel.save(root_path+'业务经理信息表_xl.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f544b97",
   "metadata": {},
   "source": [
    "以上，虽然完成了数据拆分，但是对于进一步数据处理，继续使用 openpyxl 并不是很便捷，比如数据去重，筛选等，接下来我将给大家介绍如何使用 pandas 更便捷的处理 excel 数据。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "77b1d6cf",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# 1) 读取数据\n",
    "data = pd.read_excel(root_path+'业务联系表.xlsx', header=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "d429a230",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>业务经理编号</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>客户地址</th>\n",
       "      <th>客户方负责人</th>\n",
       "      <th>性别</th>\n",
       "      <th>联系电话</th>\n",
       "      <th>备注</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>尹承望</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>何茂材</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>徐新霁</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   业务经理编号 客户名称              客户地址 客户方负责人 性别          联系电话  备注\n",
       "0       1  尹承望  *****-*****-****    孙康适  男  ***-****-*** NaN\n",
       "1       1  何茂材  *****-*****-****    孙康适  男  ***-****-*** NaN\n",
       "2       1  徐新霁  *****-*****-****    孙康适  男  ***-****-*** NaN"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2) 数据筛选处理\n",
    "# 2.1) 客户信息表\n",
    "# 筛选出 客户信息表 需要的列\n",
    "cust_info_pd = data[['业务经理编号', '客户名称', '客户地址', '客户方负责人', '性别', '联系电话', '备注']]\n",
    "# 去除重复行\n",
    "cust_info_pd.drop_duplicates(inplace=True)\n",
    "# 打印出前三行\n",
    "cust_info_pd.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "492cc33a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>业务经理编号</th>\n",
       "      <th>分区</th>\n",
       "      <th>区域</th>\n",
       "      <th>业务经理</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>北区</td>\n",
       "      <td>河北</td>\n",
       "      <td>王一磊</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    业务经理编号  分区  区域 业务经理\n",
       "0        1  南区  贵州   占亮\n",
       "5        2  南区  贵州  李朝华\n",
       "11       3  北区  河北  王一磊"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 2.2) 业务经理信息表\n",
    "# 筛选出 业务经理信息表 需要的列，并打印出前三行\n",
    "manager_info_pd = data[['业务经理编号', '分区', '区域', '业务经理']]\n",
    "# 去除重复行\n",
    "manager_info_pd.drop_duplicates(inplace=True)\n",
    "# 打印出前三行\n",
    "manager_info_pd.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "996dbcdb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 3) 数据保存\n",
    "cust_info_pd.to_excel(root_path+'客户信息表_pd.xlsx', index=None)\n",
    "manager_info_pd.to_excel(root_path+'业务经理信息表_pd.xlsx', index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "616cbfc9",
   "metadata": {},
   "source": [
    "### 2.4.2 将 客户信息表.xlsx 和 客户关系表.xlsx 合并成一个excel\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "6fbe8a94",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Int64Index: 55 entries, 0 to 54\n",
      "Data columns (total 10 columns):\n",
      " #   Column  Non-Null Count  Dtype  \n",
      "---  ------  --------------  -----  \n",
      " 0   业务经理编号  55 non-null     int64  \n",
      " 1   分区      55 non-null     object \n",
      " 2   区域      55 non-null     object \n",
      " 3   业务经理    55 non-null     object \n",
      " 4   客户名称    55 non-null     object \n",
      " 5   客户地址    55 non-null     object \n",
      " 6   客户方负责人  55 non-null     object \n",
      " 7   性别      55 non-null     object \n",
      " 8   联系电话    55 non-null     object \n",
      " 9   备注      0 non-null      float64\n",
      "dtypes: float64(1), int64(1), object(8)\n",
      "memory usage: 4.7+ KB\n"
     ]
    }
   ],
   "source": [
    "# 接上面的，将 客户信息表.xlsx 和 客户关系表.xlsx 合并成一个excel\n",
    "# 这里我们依然用 pandas 来处理\n",
    "business_contact = pd.merge(manager_info_pd, cust_info_pd, on='业务经理编号')\n",
    "# 查看合并后数据基本信息\n",
    "business_contact.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "dae38498",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>业务经理编号</th>\n",
       "      <th>分区</th>\n",
       "      <th>区域</th>\n",
       "      <th>业务经理</th>\n",
       "      <th>客户名称</th>\n",
       "      <th>客户地址</th>\n",
       "      <th>客户方负责人</th>\n",
       "      <th>性别</th>\n",
       "      <th>联系电话</th>\n",
       "      <th>备注</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "      <td>尹承望</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "      <td>何茂材</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "      <td>徐新霁</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>孙康适</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "      <td>郭承悦</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>邓翰翮</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>占亮</td>\n",
       "      <td>梁浩思</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>邓翰翮</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "      <td>毛英朗</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>邓翰翮</td>\n",
       "      <td>男</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "      <td>侯俊美</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>任敏智</td>\n",
       "      <td>女</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "      <td>许高轩</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>任敏智</td>\n",
       "      <td>女</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "      <td>段英豪</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>任敏智</td>\n",
       "      <td>女</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>南区</td>\n",
       "      <td>贵州</td>\n",
       "      <td>李朝华</td>\n",
       "      <td>汤承福</td>\n",
       "      <td>*****-*****-****</td>\n",
       "      <td>任敏智</td>\n",
       "      <td>女</td>\n",
       "      <td>***-****-***</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   业务经理编号  分区  区域 业务经理 客户名称              客户地址 客户方负责人 性别          联系电话  备注\n",
       "0       1  南区  贵州   占亮  尹承望  *****-*****-****    孙康适  男  ***-****-*** NaN\n",
       "1       1  南区  贵州   占亮  何茂材  *****-*****-****    孙康适  男  ***-****-*** NaN\n",
       "2       1  南区  贵州   占亮  徐新霁  *****-*****-****    孙康适  男  ***-****-*** NaN\n",
       "3       1  南区  贵州   占亮  郭承悦  *****-*****-****    邓翰翮  男  ***-****-*** NaN\n",
       "4       1  南区  贵州   占亮  梁浩思  *****-*****-****    邓翰翮  男  ***-****-*** NaN\n",
       "5       2  南区  贵州  李朝华  毛英朗  *****-*****-****    邓翰翮  男  ***-****-*** NaN\n",
       "6       2  南区  贵州  李朝华  侯俊美  *****-*****-****    任敏智  女  ***-****-*** NaN\n",
       "7       2  南区  贵州  李朝华  许高轩  *****-*****-****    任敏智  女  ***-****-*** NaN\n",
       "8       2  南区  贵州  李朝华  段英豪  *****-*****-****    任敏智  女  ***-****-*** NaN\n",
       "9       2  南区  贵州  李朝华  汤承福  *****-*****-****    任敏智  女  ***-****-*** NaN"
      ]
     },
     "execution_count": 56,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 查看前10条数据\n",
    "business_contact.head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "8a6de9c0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 数据保存\n",
    "manager_info_pd.to_excel(root_path+'业务联系表_pd.xlsx', index=None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0b292bbb",
   "metadata": {
    "id": "2-4-后记"
   },
   "source": [
    "## 2.5 后记\n",
    "\n",
    "- Python与Excel的自动化内容较多，此篇重在介绍基础，起到抛砖引玉的学习效果。\n",
    "- 后面还给大家介绍了 pandas 处理excel的案例，比较简单，大家实际工作、学习中可以按自己需要使用不同框架"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7a3ef8f",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "colab": {
   "name": "Task 02 Python自动化之Excel",
   "toc_visible": true
  },
  "kernelspec": {
   "display_name": "office",
   "language": "python",
   "name": "office"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.13"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "calc(100% - 180px)",
    "left": "10px",
    "top": "150px",
    "width": "561px"
   },
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
